﻿using System;
using System.Data;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.DBM;


namespace KPIS.GERP.GHRMS.BLL
{
    public class Command_DisciplineBLL
    {

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        public DataSet ListSalaryCommand()
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommand(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_seq=@cmd_seq ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommand(int cmd_type_seq ,string blank)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_type_seq", cmd_type_seq);

            DataSet ds = new DataSet();
            dbManager.Open();

            string sql;

            sql = "select * from hrm_ps_commands as PC, mas_record_statuses RS WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_type_seq=@cmd_type_seq ";
            ds = dbManager.ExecuteDataSet(CommandType.Text,sql ); 
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommandByGroup(int cmd_group_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_group_seq", cmd_group_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_commands as PC, mas_record_statuses RS,hrm_ps_command_types as CT WHERE PC.RECORD_STATUS=@RecordStatus and PC.cmd_status = RS.RECORD_STATUS and cmd_group_seq=@cmd_group_seq and PC.cmd_type_seq = CT.cmd_type_seq ");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }


        public DataTable SelectComboPenalty()
        {
            string sql;
            sql = "Select * from hrm_dc_penalty"
           +  " where record_status = '" + SystemInfo._ActiveRecordStatus + "'" ;

            DataSet ds = new DataSet();
            DataTable dt;
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text,sql);
            dbManager.Dispose();
            dt = ds.Tables[0].Copy();  
            return dt;
        }



  




        /// <summary>
        ///  Insert AppType In  By ARM
        /// </summary>
       
        public void InsertSalaryCommand(int penalty_seq,int confirm,string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, string remarks, string CreatedBy)
        {
            try
            {

                dbManager.CreateParameters(17);
                dbManager.AddParameters(0, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(1, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(2, "@cmd_name", cmd_name);
                dbManager.AddParameters(3, "@cmd_ref_no", cmd_ref_no);

                dbManager.AddParameters(4, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(5, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(6, "@cmd_status", cmd_status);
                dbManager.AddParameters(7, "@approved_seq", approved_seq);

                dbManager.AddParameters(8, "@approved_name", approved_name);
                dbManager.AddParameters(9, "@approved_positions", approved_positions);
                dbManager.AddParameters(10, "@approved_date", approved_date);
                dbManager.AddParameters(11, "@remarks", remarks);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(14, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(15, "@penalty_seq", penalty_seq );
                dbManager.AddParameters(16, "@confirm", confirm);

                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_ps_commands (";
                sql += "cmd_flag";
                sql += ",cmd_draft_no";
                sql += ",cmd_name";
                sql += ",cmd_ref_no";
                sql += ",cmd_type_seq";
                sql += ",emp_type_seq";
                sql += ",cmd_status";
                sql += ",approved_seq";
                sql += ",approved_name";
                sql += ",approved_positions";
                sql += ",approved_date";
                sql += ",remarks";
                sql += ",RECORD_STATUS";
                sql += ",created_by";
                sql += ",created_when";
                sql += ",penalty_seq";
                sql += ",confirm";
                sql += ") VALUES (";
                sql += "@cmd_flag";
                sql += ",@cmd_draft_no";
                sql += ",@cmd_name";
                sql += ",@cmd_ref_no";
                sql += ",@cmd_type_seq";
                sql += ",@emp_type_seq";
                sql += ",@cmd_status";
                sql += ",@approved_seq";
                sql += ",@approved_name";
                sql += ",@approved_positions";
                sql += ",@approved_date";
                sql += ",@remarks";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen";
                sql += ",@penalty_seq";
                sql += ",@confirm)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

        public void UpdateSalaryCommand(int Sequence, string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq, string approved_name, string approved_positions, Nullable<DateTime> approved_date, string remarks, string UpdateBy,int penalty_seq,int confirm)

        {
            try
            {
                dbManager.CreateParameters(18);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(2, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(3, "@cmd_name", cmd_name);
                dbManager.AddParameters(4, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(5, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(6, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(7, "@cmd_status", cmd_status);
                dbManager.AddParameters(8, "@approved_seq", approved_seq);
                dbManager.AddParameters(9, "@approved_name", approved_name);
                dbManager.AddParameters(10, "@approved_positions", approved_positions);
                dbManager.AddParameters(11, "@approved_date", approved_date);
                dbManager.AddParameters(12, "@remarks", remarks);
                dbManager.AddParameters(13, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(14, "@updated_by", UpdateBy);
                dbManager.AddParameters(15, "@updated_when", DateTime.Today);
                dbManager.AddParameters(16, "@penalty_seq",penalty_seq );
                dbManager.AddParameters(17, "@confirm",confirm);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_commands SET cmd_flag=@cmd_flag, cmd_draft_no=@cmd_draft_no,cmd_name=@cmd_name,cmd_ref_no=@cmd_ref_no,cmd_type_seq=@cmd_type_seq,emp_type_seq=@emp_type_seq,cmd_status=@cmd_status,approved_seq=@approved_seq,approved_name=@approved_name,approved_positions=@approved_positions,approved_date=@approved_date,remarks=@remarks,record_status=@RecordStatus,updated_by=@updated_by,updated_when=@updated_when,penalty_seq=@penalty_seq,confirm=@confirm WHERE cmd_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void DeleteSalaryCommand(int Sequence, string UpdateBy)
        { 
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_ps_commands SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE cmd_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        

        //=====================================================

        public DataSet ListSalaryCommandDetail(int cmd_dtls_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *, PCD.remarks as pcd_remarks from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PCD.cmd_dtls_seq = @cmd_dtls_seq");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }




        public DataTable ListDisciplineCommandPopup(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();

            string sql;

            sql = "SELECT "
+ " dtl.crime_section,"
+ " Concat(prefix.prefix_name,'  ',emp.first_name,'    ' ,emp.last_name) as full_name,"
+ " dtl.pos_name,"
+ " dtl.crime_descr,"
+ " dtl.job_site_name,"
+ " dtl.cmd_dtls_seq,"
+ " dtl.class_seq,"
+ " dtl.pos_ref,"
+ " dtl.pos_name,"
+ " dtl.class_name,"
+ " dtl.crime_section,"
+ " dtl.pen_level_seq,"
+ " dtl.salary,"
+ " dtl.emp_seq,"
+ " dtl.crd_seq,"
+ " dtl.crime_year"
+ " FROM"
+ " hrm_dc_command_dtls AS dtl"
+ " Inner Join hrm_ps_employees AS emp ON dtl.emp_seq = emp.emp_seq"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where dtl.cmd_seq = " + cmd_seq
+ " and dtl.record_status = '" + SystemInfo._ActiveRecordStatus + "'";  


            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            DataTable dt;
            dt = ds.Tables[0].Copy();  
            return dt;
        }


        public DataTable ListSanction(string cmd_dtls_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_dtls_seq);

            DataSet ds = new DataSet();

            string sql;

            sql = "SELECT "

+ " dtl.crime_section,"
+ " Concat(prefix.prefix_name,'  ',emp.first_name,'    ' ,emp.last_name) as full_name,"
+ " dtl.pos_name,"
+ " dtl.crime_descr,"
+ " dtl.job_site_name,"
+ " dtl.cmd_dtls_seq,"
+ " dtl.pos_ref,"
+ " dtl.class_seq,"
+ " dtl.crime_year,"
+ " dtl.cmd_seq,"
+ " dtl.cmd_name,"
+ " dtl.emp_seq,"
+ " dtl.pos_seq,"
+ " dtl.job_site_seq,"
+ " dtl.salary,"
+ " dtl.deduct_percent,"
+ " dtl.deduct_level,"
+ " dtl.deduct_amount,"
+ " dtl.deduct_salary,"
+ " dtl.cmm_type,"
+ " dtl.cmm_ref_no,"
+ " dtl.cmm_date,"
+ " dtl.cmm_deduct_percent,"
+ " dtl.cmm_level,"
+ " dtl.cmm_penalty_seq,"
+ " dtl.pen_level_seq,"
+ " dtl.kopo_ref_no,"
+ " dtl.start_date,"
+ " dtl.end_date,"
+ " dtl.remarks,"
+ " dtl.deduct_month,"
+ " dtl.cmm_month,"
+ " dtl.class_name,"
+ " dtl.pen_seq,"
+ " dtl.crd_seq"
+ " FROM"
+ " hrm_dc_command_dtls AS dtl"
+ " Inner Join hrm_ps_employees AS emp ON dtl.emp_seq = emp.emp_seq"
+ " Inner Join mas_name_prefixes AS prefix ON emp.name_prefix_seq = prefix.name_prefix_seq"
+ " where dtl.cmd_dtls_seq = " + cmd_dtls_seq
+ " and dtl.record_status = '" + SystemInfo._ActiveRecordStatus + "'";


            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            DataTable dt;
            dt = ds.Tables[0].Copy();
            return dt;
        }



        public DataSet ListSalaryCommandDetail(string cmd_seq)
        {
            dbManager.CreateParameters(2);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@cmd_seq", cmd_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select *, CONCAT( prefix_name, first_name,' ',last_name ) full_name,PCD.remarks as pcd_remarks from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC,mas_name_prefixes as NP WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PE.name_prefix_seq = NP.name_prefix_seq and PCD.cmd_seq = @cmd_seq");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }

        public DataSet ListSalaryCommandDetail(string cmd_seq, string emp_seq)
        {
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_command_dtls as PCD, hrm_ps_commands as PC, hrm_ps_employees as PE, hrm_mp_classes as MC WHERE PCD.RECORD_STATUS=@RecordStatus and PCD.cmd_seq = PC.cmd_seq and PCD.emp_seq = PE.emp_seq and PCD.new_class_seq = MC.class_seq and PCD.cmd_seq = " + cmd_seq + " and PCD.emp_seq in (" + emp_seq + ")");
            dbManager.Dispose();
            //return DAL.ListInquire(new InquireInfo(0));
            return ds;
        }




        public DataTable ListDialogDisciplineCommandEmp(int inv_seq)
        {
         

            DataSet ds = new DataSet();
            dbManager.Open();


            string sql;

            sql = " SELECT "
+ " Concat(mas_name_prefixes.prefix_name,' ',hrm_ps_employees.first_name,'    ',hrm_ps_employees.last_name) as FullName,"
+ " hrm_mp_positions.pos_name,"
+ " hrm_mp_classes.class_name,"
+ " ifNULL(hrm_ps_employees.working_job_site_seq,hrm_mp_job_sites3.job_site_seq) AS working_job_site_seq,"
+ " ifNULL(hrm_mp_job_sites2.job_site_name,hrm_mp_job_sites3.job_site_name) AS working_job_site_name,"
+ " hrm_mp_job_sites3.job_site_seq,"
+ " hrm_mp_job_sites3.job_site_name,"
+ " hrm_mp_positions.pos_ref,"
+ " hrm_ps_employees.salary,"
+ " hrm_ps_employees.emp_seq"
+ " FROM"
+ " hrm_ps_employees"
+ " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq"
+ " Inner Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq"
+ " Inner Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq"
+ " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites2.job_site_seq"
+ " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_mp_positions.job_site_seq = hrm_mp_job_sites3.job_site_seq"
+ " Inner Join hrm_dc_investigation_dtls ON hrm_dc_investigation_dtls.inv_emp_seq = hrm_ps_employees.emp_seq"
+ " WHERE"
+ " hrm_dc_investigation_dtls.inv_seq = " + inv_seq ;



            ds = dbManager.ExecuteDataSet(CommandType.Text,sql);
            dbManager.Dispose();
            DataTable dt;
            dt = ds.Tables[0].Copy();  
            return dt;
        }



        public bool ChkDuplicate(int emp_seq)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, "select * from hrm_ps_command_dtls where emp_seq = " + emp_seq + " and record_status = '" + SystemInfo._ActiveRecordStatus + "'");
            if (ds.Tables[0].Rows.Count != 0)
                return true;
            else
                return false;
        }


     


        public void InsertDisciplineCommandDetail(int cmd_seq,int crd_seq, int emp_seq,int pen_level_seq,
            string pos_ref,string pos_name,string class_name,string job_site_name,double  salary,
            string crime_descr,string crime_section,int crime_year, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@pen_level_seq", pen_level_seq);

                dbManager.AddParameters(3, "@pos_ref", pos_ref);
                dbManager.AddParameters(4, "@pos_name", pos_name);
                dbManager.AddParameters(5, "@class_name", class_name);
                dbManager.AddParameters(6, "@job_site_name", job_site_name);
                dbManager.AddParameters(7, "@salary", salary);

                dbManager.AddParameters(8, "@crime_descr", crime_descr);
                dbManager.AddParameters(9, "@crime_section", crime_section);
                dbManager.AddParameters(10, "@crime_year", crime_year);
                dbManager.AddParameters(11, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(13, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(14, "@crd_seq", crd_seq);


                dbManager.Open();
                string sql;
                sql = "INSERT INTO hrm_dc_command_dtls (";
                sql += "cmd_seq";
                sql += ",emp_seq";

                sql += ",pen_level_seq";
                sql += ",pos_ref";
                sql += ",pos_name";
                sql += ",class_name";
                sql += ",job_site_name";
                sql += ",salary";
                sql += ",crd_seq";
             

                sql += ",crime_descr";
                sql += ",crime_section";
                sql += ",crime_year";
                sql += ",record_status";
                sql += ",created_by";
                sql += ",created_when";
                
                sql += ") VALUES (";
                sql += "@cmd_seq";
                sql += ",@emp_seq";

                sql += ",@pen_level_seq";
                sql += ",@pos_ref";
                sql += ",@pos_name";
                sql += ",@class_name";
                sql += ",@job_site_name";
                sql += ",@salary";
                sql += ",@crd_seq";


                sql += ",@crime_descr";
                sql += ",@crime_section";
                sql += ",@crime_year";
                sql += ",@record_status";
                sql += ",@CreatedBy";
                sql += ",@CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }




        public void UpdateDisciplineCommandDetail(int cmd_dtls_seq, int crd_seq,int pen_level_seq,
                  string crime_descr, string crime_section, int crime_year, string update_by)
        {
            try
            {
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@pen_level_seq", pen_level_seq);
                dbManager.AddParameters(2, "@crime_descr", crime_descr);
                dbManager.AddParameters(3, "@crime_section", crime_section);
                dbManager.AddParameters(4, "@crime_year", crime_year);
                dbManager.AddParameters(5, "@updated_by", update_by);
                dbManager.AddParameters(6, "@updated_when", DateTime.Today);
                dbManager.AddParameters(7, "@crd_seq", crd_seq);


                dbManager.Open();
                string sql;
                sql = "update hrm_dc_command_dtls set";
                sql += " pen_level_seq=@pen_level_seq";
                sql += ",crd_seq=@crd_seq";
                sql += ",crime_descr=@crime_descr";
                sql += ",crime_section=@crime_section";
                sql += ",crime_year=@crime_year";
                sql += ",updated_by=@updated_by";
                sql += ",updated_when=@updated_when";
                sql += " where cmd_dtls_seq=@cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        private string formatDate(string value)
        {
            string tmp = null;
            string[] tmp2 = null;
            tmp2 = value.Split('/');
            tmp = tmp2[1] + "/" + tmp2[0] + "/" + tmp2[2];

            return tmp;
        }


        public void UpdateSanction1(int cmd_dtls_seq, string cmm_type,string cmm_ref_no,
            string cmm_date,int cmm_penalty_seq,string kopo_ref_no,string start_date,string update_by)
        {
            try
            {

                cmm_date = Utils.Date.ToEngFormatDate(cmm_date);
                start_date = Utils.Date.ToEngFormatDate(start_date);
            
                
                DateTime m_cmm_date,m_start_date;

                cmm_date = formatDate(cmm_date);
                m_cmm_date = Convert.ToDateTime(cmm_date);
  
                start_date = formatDate(start_date);
                m_start_date = Convert.ToDateTime(start_date);  
                





                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@cmm_type", cmm_type);
                dbManager.AddParameters(2, "@cmm_ref_no", cmm_ref_no);
                dbManager.AddParameters(3, "@cmm_date", m_cmm_date);
                dbManager.AddParameters(4, "@cmm_penalty_seq", cmm_penalty_seq);
                dbManager.AddParameters(5, "@kopo_ref_no", kopo_ref_no);
                dbManager.AddParameters(6, "@start_date", m_start_date);
                dbManager.AddParameters(7, "@updated_by", update_by);
                dbManager.AddParameters(8, "@updated_when", DateTime.Today);


                dbManager.Open();
                string sql;
                sql = "update hrm_dc_command_dtls set";

                sql += " cmm_type=@cmm_type";
                sql += ",cmm_ref_no=@cmm_ref_no";
                sql += ",cmm_date=@cmm_date";
                sql += ",cmm_penalty_seq=@cmm_penalty_seq";
                sql += ",kopo_ref_no=@kopo_ref_no";
                sql += ",start_date=@start_date";
                sql += ",updated_by=@updated_by";
                sql += ",updated_when=@updated_when";
                sql += " where cmd_dtls_seq=@cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }


        public void UpdateSanction2(int cmd_dtls_seq, string cmm_type, string cmm_ref_no,
           string cmm_date, int cmm_penalty_seq, string kopo_ref_no, string start_date, string update_by,
            decimal deduct_percent, int deduct_month, decimal cmm_deduct_percent, int cmm_month)
        {
            try
            {
                cmm_date = Utils.Date.ToEngFormatDate(cmm_date);
                start_date = Utils.Date.ToEngFormatDate(start_date);


                DateTime m_cmm_date, m_start_date;

                cmm_date = formatDate(cmm_date);
                m_cmm_date = Convert.ToDateTime(cmm_date);

                start_date = formatDate(start_date);
                m_start_date = Convert.ToDateTime(start_date);

               
 



                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@cmm_type", cmm_type);
                dbManager.AddParameters(2, "@cmm_ref_no", cmm_ref_no);
                dbManager.AddParameters(3, "@cmm_date", m_cmm_date);
                dbManager.AddParameters(4, "@cmm_penalty_seq", cmm_penalty_seq);
                dbManager.AddParameters(5, "@kopo_ref_no", kopo_ref_no);
                dbManager.AddParameters(6, "@start_date", m_start_date);
                dbManager.AddParameters(7, "@updated_by", update_by);
                dbManager.AddParameters(8, "@updated_when", DateTime.Today);

                dbManager.AddParameters(9, "@deduct_percent", deduct_percent);
                dbManager.AddParameters(10, "@deduct_month", deduct_month);
                dbManager.AddParameters(11, "@cmm_deduct_percent", cmm_deduct_percent);
                dbManager.AddParameters(12, "@cmm_month", cmm_month);


                dbManager.Open();
                string sql;
                sql = "update hrm_dc_command_dtls set";

                sql += " cmm_type=@cmm_type";
                sql += ",cmm_ref_no=@cmm_ref_no";
                sql += ",cmm_date=@cmm_date";
                sql += ",cmm_penalty_seq=@cmm_penalty_seq";
                sql += ",kopo_ref_no=@kopo_ref_no";
                sql += ",start_date=@start_date";
                sql += ",updated_by=@updated_by";
                sql += ",updated_when=@updated_when";

                sql += ",deduct_percent=@deduct_percent";
                sql += ",cmm_deduct_percent=@cmm_deduct_percent";
                sql += ",cmm_month=@cmm_month";
          
                sql += ",deduct_month=@deduct_month";

                sql += " where cmd_dtls_seq=@cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public void UpdateSanction3(int cmd_dtls_seq, string cmm_type, string cmm_ref_no,
    string cmm_date, int cmm_penalty_seq, string kopo_ref_no, string start_date, string update_by,
     decimal deduct_level,decimal cmm_level)
        {
            try
            {

                cmm_date = Utils.Date.ToEngFormatDate(cmm_date);
                start_date = Utils.Date.ToEngFormatDate(start_date);


                DateTime m_cmm_date, m_start_date;

                cmm_date = formatDate(cmm_date);
                m_cmm_date = Convert.ToDateTime(cmm_date);

                start_date = formatDate(start_date);
                m_start_date = Convert.ToDateTime(start_date);






                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@cmm_type", cmm_type);
                dbManager.AddParameters(2, "@cmm_ref_no", cmm_ref_no);
                dbManager.AddParameters(3, "@cmm_date", m_cmm_date);
                dbManager.AddParameters(4, "@cmm_penalty_seq", cmm_penalty_seq);
                dbManager.AddParameters(5, "@kopo_ref_no", kopo_ref_no);
                dbManager.AddParameters(6, "@start_date", m_start_date);
                dbManager.AddParameters(7, "@updated_by", update_by);
                dbManager.AddParameters(8, "@updated_when", DateTime.Today);

                dbManager.AddParameters(9, "@deduct_level", deduct_level);
                dbManager.AddParameters(10, "@cmm_level", cmm_level );
               


                dbManager.Open();
                string sql;
                sql = "update hrm_dc_command_dtls set";

                sql += " cmm_type=@cmm_type";
                sql += ",cmm_ref_no=@cmm_ref_no";
                sql += ",cmm_date=@cmm_date";
                sql += ",cmm_penalty_seq=@cmm_penalty_seq";
                sql += ",kopo_ref_no=@kopo_ref_no";
                sql += ",start_date=@start_date";
                sql += ",updated_by=@updated_by";
                sql += ",updated_when=@updated_when";

                sql += ",deduct_level=@deduct_level";
                sql += ",cmm_level=@cmm_level";
  
                sql += " where cmd_dtls_seq=@cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }



        public DataTable SetSession(int cmd_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " cmd.cmd_seq,"
+ " cmd.penalty_seq,"
+ " pen.pen_name,"
+ " pen.pen_seq"
+ " FROM"
+ " hrm_ps_commands AS cmd"
+ " Inner Join hrm_dc_penalty AS pen ON pen.pen_seq = cmd.penalty_seq"

+ " where cmd.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and cmd.cmd_seq = " + cmd_seq;


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }



        public void DeleteDisciplineCommandDetail(int Sequence, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE hrm_dc_command_dtls SET RECORD_STATUS=@record_status,updated_by=@updated_by, updated_when=@updated_when WHERE cmd_dtls_seq=@sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }

    }
}
